﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.IO;
using System.Reflection;
using System.Text;
using System.Xml;
using Microsoft.BizTalk.Agent.Interop;
using Microsoft.BizTalk.Component.Interop;
using Microsoft.BizTalk.Message.Interop;
using Microsoft.Win32;
namespace BTSDecompress.ClassLibrary
{
    static public class Utils
    {
        #region Variables
        static DataTable _dataTable = new DataTable();
        static string _bizTalkInstallationFolder = "";
        #endregion
        #region Methods
        static string AddAlias(CustomFunction customFunction, string query)
        {

            if (customFunction.ColumnAlias.Length > 0)
            {
                customFunction.Key = customFunction.ColumnAlias;

                return query;
            }
            else
            {
                // Retrieve startposition and fieldname
                int position = customFunction.StartPosition;
                string fieldName = customFunction.MessageField;
                int length = fieldName.Length + customFunction.TableAlias.Length;

                string sql1 = query.Substring(0, position + length);
                string sql2 = query.Substring(position + length);

                int fieldFounter = sql1.Split(',').Length;
                customFunction.Key = fieldName + fieldFounter;
                return sql1 + " " + customFunction.Key + sql2;
            }
        }
        static string AddColumnNames(SqlQuery sqlQuery, SqlDataReader reader, string message)
        {
            // Show the column names (if checked)
            if (sqlQuery.ShowColumnNames)
            {
                for (int j = 0; j < reader.FieldCount; j++)
                {
                    string key = reader.GetName(j) + (j + 1).ToString();
                    
                    if (sqlQuery.CustomFunctions.ContainsKey(key))
                    {
                        CustomFunction customFunction = (CustomFunction)sqlQuery.CustomFunctions[key];
                        //message = string.Concat(message, reader.GetName(j), (sqlQuery.UseFieldSeparator ? "; " : " "));
                        message = string.Concat(message, customFunction.MessageField + (j + 1).ToString(), (sqlQuery.UseFieldSeparator ? "; " : " "));
                        _dataTable.Columns.Add(customFunction.MessageField + (j + 1).ToString());
                    }
                    else
                    {
                        message = string.Concat(message, reader.GetName(j), (sqlQuery.UseFieldSeparator ? "; " : " "));
                        _dataTable.Columns.Add(reader.GetName(j));
                    }
                }

                message += "\r\n";
            }
            else 
            {
                for (int j = 0; j < reader.FieldCount; j++)
                {
                    if (sqlQuery.CustomFunctions.ContainsKey(reader.GetName(j) + (j + 1).ToString()))
                    {
                        CustomFunction customFunction = (CustomFunction)sqlQuery.CustomFunctions[reader.GetName(j) + (j + 1).ToString()];
                        _dataTable.Columns.Add(customFunction.MessageField + (j + 1).ToString());
                    }
                    else
                    {
                        _dataTable.Columns.Add(reader.GetName(j));
                    }
                }
            }

            return message;
        }
        static private void Cleanup()
        {
            //sqlQuery.Clear();
            _dataTable = new DataTable();
        }
        static public string ExecuteQuery(SqlQuery sqlQuery, out DataTable dataTable)
        {
            SqlDataReader reader;
            _dataTable = new DataTable();
            try
            {
                _bizTalkInstallationFolder = GetBizTalkInstallationFolder();

                // Connection to database 
                SqlConnection con = new SqlConnection("Data Source=" + sqlQuery.DatabaseServer + ";Initial Catalog=" + sqlQuery.Database + ";Integrated Security=True");

                string message = "";

                try
                {
                    SqlCommand cmd = new SqlCommand();

                    // Check if the query contains a custom function and if so,
                    // store its values and strip it from the query
                    string sqlquery = null;
                    bool hasFunction = ParseQuery(sqlQuery.CommandText);
                    sqlquery = (hasFunction == true ? ParseToSQLQuery(sqlQuery, sqlQuery.CommandText) : sqlQuery.CommandText);

                    // Set the command and execute the query against the database
                    cmd.CommandText = sqlquery;
                    cmd.CommandType = CommandType.Text;
                    cmd.Connection = con;
                    con.Open();
                    reader = cmd.ExecuteReader();

                    int nrOfRecords = 0;

                    // Show the column names (if checked)
                    message = AddColumnNames(sqlQuery, reader, message);

                    //Get the reader to retrieve the data
                    while (reader.Read())
                    {

                        Object[] itemArray = new Object[_dataTable.Columns.Count];

                        // Iterate through all fields
                        for (int j = 0; j < reader.FieldCount; j++)
                        {
                            string field = ParseField(sqlQuery, reader, j);
                            message = string.Concat(message, field, (sqlQuery.UseFieldSeparator == true ? "; " : " "));
                            itemArray[j] = field;
                        }

                        // If requested add an extra blank line for better readability
                        if (sqlQuery.BlankLineBetweenRecords == true)
                        {
                            message += "\r\n";
                        }

                        // Add the row to the DataTable
                        _dataTable.Rows.Add(itemArray);

                        // Add Newline
                        message += "\r\n";

                        // Increase recordcounter
                        nrOfRecords++;
                    }

                }
                finally
                {
                    dataTable = _dataTable;
                    con.Close();
                }

                return message;

            }
            catch (Exception exSQL)
            {
                return "Failed: " + exSQL.Message;
            }
            finally
            {
                dataTable = _dataTable;
                Cleanup();
            }
        }
        static string GetBizTalkInstallationFolder()
        {
            RegistryKey registry;
            string installPath="";
            try
            {
                registry = Registry.LocalMachine.OpenSubKey("SOFTWARE\\Microsoft\\BizTalk Server\\3.0");
                installPath = registry.GetValue("InstallPath", String.Empty).ToString();
            }
            catch(System.Security.SecurityException ex)
            {
                return ex.Message;
            }
            catch (System.ObjectDisposedException ex)
            {
                return ex.Message;
            }
            catch (System.IO.IOException ex)
            {
                return ex.Message;
            }
            catch (System.UnauthorizedAccessException ex)
            {
                return ex.Message;
            }
            return installPath;
        }
        static public string LoadFile(SqlQuery sqlQuery)
        {
            string[] lines = System.IO.File.ReadAllLines(sqlQuery.File);

            foreach (string line in lines)
            {

                // Build up query
                if (!line.StartsWith("--"))
                {
                    sqlQuery.CommandText += string.Format("{0}\r\n", line);
                }

                // Get first 31 characters of the line 
                // (this is a little bit more than the length of the longest option)
                string setting = line.Substring(0, line.Length > 31 ? 31 : line.Length);

                switch (setting.ToLower().TrimEnd())
                {
                    case "-- database server":
                        {
                            // In case a property already has a value (from the command line),
                            // don't override this value
                            if (sqlQuery.DatabaseServer == "")
                            {
                                sqlQuery.DatabaseServer = line.Substring(33);
                            }
                            break;
                        }
                    case "-- database":
                        {
                            if (sqlQuery.Database == "")
                            {
                                sqlQuery.Database = line.Substring(33);
                            }
                            break;
                        }
                    case "-- namespace":
                        {
                            try
                            {
                                string[] ns = line.Substring(33).Split(' ');
                                sqlQuery.Namespaces.Add(ns[0].ToString(), ns[1].ToString());
                            }
                            catch (Exception ex)
                            {
                                throw;
                                //MessageBox.Show("Error loading Namespace.\r\n{0}", ex.Message); 
                            }
                            break;
                        }
                    case "-- hide outer xml tag":
                        {
                            sqlQuery.HideOuterXMLTag = Convert.ToBoolean(line.Substring(33));
                            break;
                        }
                    case "-- hide context property names":
                        {
                            sqlQuery.HideContextPropertyNames = Convert.ToBoolean(line.Substring(33));
                            break;
                        }
                    case "-- show column names":
                        {
                            if (sqlQuery.ShowColumnNames == false)
                            {
                                sqlQuery.ShowColumnNames = Convert.ToBoolean(line.Substring(33));
                            }
                            break;
                        }
                    case "-- use field seperator":
                        {
                            sqlQuery.UseFieldSeparator = Convert.ToBoolean(line.Substring(33));
                            break;
                        }
                    case "-- blank line between records":
                        {
                            sqlQuery.BlankLineBetweenRecords = Convert.ToBoolean(line.Substring(33));
                            break;
                        }
                    case "-- word wrap":
                        {
                            sqlQuery.WordWrap = Convert.ToBoolean(line.Substring(33));
                            break;
                        }
                    default:
                        { break; }
                }
            }
            return sqlQuery.CommandText;
        }
        static string ParseContextField(byte[] contxt, string ctxProperties, bool bHideContextPropertyName)
        {
            string message = null;

            try
            {
                // Load Context
                MemoryStream stream = new MemoryStream((contxt)); // result of calling ops_LoadTrackedMessageContext
                IBaseMessageContext context = ((IBTMessageAgentFactory)((IBTMessageAgent)new BTMessageAgent())).CreateMessageContext();
                ((IPersistStream)context).Load(stream);

                // Iterate through all Context Properties
                for (int i = 0; i < context.CountProperties - 1; ++i)
                {
                    string propName;
                    string propNamespace;
                    object propValue = context.ReadAt(i, out propName, out propNamespace);

                    if (ctxProperties == null || ctxProperties.ToLower().Contains(propName.ToLower()))
                    {
                        if (!bHideContextPropertyName)
                        {
                            message += propName.ToString() + " = ";
                        }
                        message += propValue.ToString() + ", ";
                    }
                }
            }
            catch (Exception ex)
            {
                message += ex.Message;
                throw;
            }

            if (message == null || message.Length == 0)
            {
                message = Constants.NULL_VALUE + ", ";
            }

            message = message.Substring(0, message.Length - 2);
            return message;

        }
        static string ParseEbXML(StreamReader stream, SqlQuery sqlQuery, string ebXmlOption, string xpath, bool hideOuterXMLTag)
        {
            // Initialize variables which will hold the SOAP Envelope and the payload
            string message = "";
            string soapEnvelopeXml = "";
            string payloadXml = "";
            string partName = "";
            string partNameEnd = "";

            // Initialize some more variables
            string line = "";
            int lineCount = 0;
            Boolean addToSoapEnvelope = false;
            Boolean SoapEnvelopeProcessed = false;
            Boolean addToPayload = false;

            // Reset the stream to the first position
            stream.DiscardBufferedData();
            stream.BaseStream.Seek(0, SeekOrigin.Begin);
            stream.BaseStream.Position = 0;

            // Iterate through the stream
            while (stream.Peek() > -1)
            {
                // Read a line from the stream
                line = stream.ReadLine();
                message += line;

                // The first line contains the part name, which is used to identify the different parts of the message
                if (lineCount == 0)
                {
                    partName = line;
                    partNameEnd = partName + "--";
                    lineCount++;
                }

                // If the line starts with the SOAP Envelope start tag, start writing to the XML string
                if (line.Contains(Constants.SOAP_ENVELOPE_START_TAG))
                {
                    addToSoapEnvelope = true;
                }

                // Write to the XML String
                if (addToSoapEnvelope)
                {
                    soapEnvelopeXml += line;
                }

                // If the line starts with the SOAP Envelope end tag, stop writing to the XML string
                if (line.Contains(Constants.SOAP_ENVELOPE_END_TAG))
                {
                    addToSoapEnvelope = false;
                    SoapEnvelopeProcessed = true;
                }

                // If the SOAP Envelope is processed and the line does not start with the part name or 'Content-',
                // start writing to the payload variable, until the end part name tag is found
                if (SoapEnvelopeProcessed == true && line.Contains(Constants.SOAP_ENVELOPE_END_TAG) == false)
                {
                    addToPayload = true;
                }

                // Start writing the payload (under some conditions)
                if (addToPayload == true && line.StartsWith(partName) == false && line.StartsWith(partNameEnd) == false && line.StartsWith("Content-") == false)
                {
                    payloadXml += line;
                }
            }

            // The message is processed and stored in the variables soapEnvelopeXML and payloadXML
            // Now let's get the requested data (SOAP Envelope or ebXML payload) and return it
            if (ebXmlOption == "ebenvelope")
            {
                return (Utils.RetrieveData(soapEnvelopeXml, xpath, sqlQuery.Namespaces, hideOuterXMLTag));
            }

            if (ebXmlOption == "ebpayload")
            {
                return (RetrieveData(payloadXml, xpath, sqlQuery.Namespaces, hideOuterXMLTag));
            }

            if (xpath == null)
            {
                return message;
            }
            else
            {
                return Constants.NULL_VALUE;
            }
        }
        static string ParseField(SqlQuery sqlQuery, SqlDataReader reader, int counter)
        {
            // Determine fieldname
            string fieldName = reader.GetName(counter);

            CustomFunction customFunction = (CustomFunction)sqlQuery.CustomFunctions[fieldName];

            if (customFunction != null)
            {
                switch (customFunction.Type)
                {
                    case Enums.CustomFunctions.MessageToText:
                        {
                            try
                            {
                                // Here the message content is processed
                                if (Convert.IsDBNull(reader[fieldName]) == false)
                                {
                                    string msg = Utils.ParseMessageField((byte[])reader[fieldName], sqlQuery, customFunction);
                                    return msg;
                                }
                                else
                                {
                                    return Constants.NULL_VALUE;
                                }
                            }
                            catch (Exception ex)
                            {
                                return ex.Message;
                            }
                        }
                    case Enums.CustomFunctions.ContextToText:
                        {
                            try
                            {
                                // Here the context Database is processed
                                if (Convert.IsDBNull(reader[fieldName]) == false)
                                {
                                    string msg = Utils.ParseContextField((byte[])reader[fieldName], customFunction.ContextProperties, sqlQuery.HideContextPropertyNames);
                                    return msg;
                                }
                                else
                                {
                                    return Constants.NULL_VALUE;
                                }
                            }
                            catch (Exception ex)
                            {
                                return ex.Message;
                            }
                        }
                    case Enums.CustomFunctions.ByteArray:
                        {
                            try
                            {
                                // Here the message content is processed
                                if (Convert.IsDBNull(reader[fieldName]) == false)
                                {
                                    string msg = Utils.ParsePayloadField((byte[])reader[fieldName], sqlQuery, customFunction);
                                    return msg;
                                }
                                else
                                {
                                    return Constants.NULL_VALUE;
                                }
                            }
                            catch (Exception ex)
                            {
                                return ex.Message;
                            }
                        }
                    default:
                        {
                            return reader[counter].ToString();
                        }
                }
            }

            string val = reader[counter].ToString();

            return reader[counter] == DBNull.Value ? Constants.NULL_VALUE : reader[counter].ToString();

            //return reader[counter].ToString();
        }
        static string ParseMessageField(byte[] msg, SqlQuery sqlQuery, CustomFunction customFunction)
        {

            string message = "";

            try
            {
                //Use memory stream and reflection to get the data
                SqlBinary binData = new SqlBinary(msg);
                MemoryStream stream = new MemoryStream(binData.Value);
                Assembly pipelineAssembly = Assembly.LoadFrom(string.Concat(_bizTalkInstallationFolder, @"\Microsoft.BizTalk.Pipeline.dll"));
                Type compressionStreamsType = pipelineAssembly.GetType("Microsoft.BizTalk.Message.Interop.CompressionStreams", true);
                StreamReader st = new StreamReader((Stream)compressionStreamsType.InvokeMember("Decompress", BindingFlags.Public | BindingFlags.InvokeMethod | BindingFlags.Static, null, null, new object[] { (object)stream }));
                message = st.ReadToEnd();

                // If query on ebXML exists
                if (message.StartsWith("<") == false)
                {
                    message = ParseEbXML(st, sqlQuery, customFunction.EbXmlOption, customFunction.Query, sqlQuery.HideOuterXMLTag);
                }
                else
                // If xpath query exists (but not on ebXML)
                //if (xpath != null && ebXmlOption == "")
                {
                    message = ParseXML(sqlQuery, message, customFunction.Query, sqlQuery.HideOuterXMLTag);
                }

            }
            catch (Exception ex)
            {
                message = ex.Message;
            }

            return message;
        }
        static public void ParseParameters(string[] args, SqlQuery sqlQuery)
        {
            foreach (string arg in args)
            {
                //Console.WriteLine(arg);

                // Database server
                if (arg.ToLower().StartsWith("-s:"))
                {
                    sqlQuery.DatabaseServer = arg.Substring(3);
                }

                // Database
                if (arg.ToLower().StartsWith("-d:"))
                {
                    sqlQuery.Database = arg.Substring(3);
                }

                // SQL File
                if (arg.ToLower().StartsWith("-sqlfile:"))
                {
                    sqlQuery.File = arg.Substring(9);
                }

                // Output File
                if (arg.ToLower().StartsWith("-outputfile:"))
                {
                    sqlQuery.OutputFile = arg.Substring(12);
                }

                // Overwrite Output File
                if (arg.ToLower().StartsWith("-overwriteoutputfile"))
                {
                    sqlQuery.OverwriteOutputFile = true;
                }

                // Show Column Names
                if (arg.ToLower().StartsWith("-showcolumnnames"))
                {
                    sqlQuery.ShowColumnNames = true;
                }
            }
        }
        static string ParsePayload(StreamReader stream, SqlQuery sqlQuery, CustomFunction customFunction, bool hideOuterXMLTag)
        {
            // Initialize variables which will hold the SOAP Envelope and the payload
            string message = "";
            string soapEnvelopeXml = "";
            string partName = "";
            string partNameEnd = "";
            string payloadXml = "";

            // Initialize some more variables
            string line = "";
            int lineCount = 0;
            Boolean addToSoapEnvelope = false;
            Boolean SoapEnvelopeProcessed = false;
            Boolean addToPayload = false;

            // Reset the stream to the first position
            stream.DiscardBufferedData();
            stream.BaseStream.Seek(0, SeekOrigin.Begin);
            stream.BaseStream.Position = 0;

            // Iterate through the stream
            while (stream.Peek() > -1)
            {
                // Read a line from the stream
                line = stream.ReadLine();
                message += line;

                // The first line contains the part name, which is used to identify the different parts of the message
                if (lineCount == 0)
                {
                    partName = line;
                    partNameEnd = partName + "--";
                    lineCount++;
                }

                // If the line starts with the SOAP Envelope start tag, start writing to the XML string
                if (line.Contains(Constants.SOAP_ENVELOPE_START_TAG2))
                {
                    addToSoapEnvelope = true;
                }

                // Write to the XML String
                if (addToSoapEnvelope)
                {
                    soapEnvelopeXml += line;
                }

                // If the line starts with the SOAP Envelope end tag, stop writing to the XML string
                if (line.Contains(Constants.SOAP_ENVELOPE_END_TAG2))
                {
                    addToSoapEnvelope = false;
                    SoapEnvelopeProcessed = true;
                }

                // If the SOAP Envelope is processed and the line does not start with the part name or 'Content-',
                // start writing to the payload variable, until the end part name tag is found
                if (SoapEnvelopeProcessed == true && line.Contains(Constants.SOAP_ENVELOPE_START_TAG2) == false)
                {
                    addToPayload = true;
                }

                // Start writing the payload (under some conditions)
                if (addToPayload == true && line.StartsWith(partName) == false && line.StartsWith(partNameEnd) == false && line.StartsWith("Content-") == false)
                {
                    payloadXml += line;
                }
            }

            // The message is processed and stored in the variables soapEnvelopeXML and payloadXML
            // Now let's get the requested data (SOAP Envelope or ebXML payload) and return it
            if (customFunction.EbXmlOption.ToLower() == "ebenvelope")
            {
                return (RetrieveData(soapEnvelopeXml, customFunction.Query, sqlQuery.Namespaces, hideOuterXMLTag));
            }

            if (customFunction.EbXmlOption.ToLower() == "ebpayload")
            {
                return (RetrieveData(payloadXml, customFunction.Query, sqlQuery.Namespaces, hideOuterXMLTag));
            }

            if (customFunction.Query == null)
            {
                return message;
            }
            else
            {
                return Constants.NULL_VALUE;
            }
        }
        static string ParsePayloadField(byte[] msg, SqlQuery sqlQuery, CustomFunction customFunction)
        {
            string message = "";
            //message = Encoding.ASCII.GetString((byte[])msg);

            try
            {
                //Use memory stream and reflection to get the data
                SqlBinary binData = new SqlBinary(msg);
                MemoryStream stream = new MemoryStream(binData.Value);
                StreamReader st = new StreamReader((Stream)stream);
                message = st.ReadToEnd();

                // If query on ebXML exists
                if (message.StartsWith("<") == false)
                {
                    message = ParsePayload(st, sqlQuery, customFunction, sqlQuery.HideOuterXMLTag);
                }
                else
                // If xpath query exists (but not on ebXML)
                //if (xpath != null && ebXmlOption == "")
                {
                    message = ParseXML(sqlQuery, message, customFunction.Query, sqlQuery.HideOuterXMLTag);
                }

            }
            catch (Exception ex)
            {
                message = ex.Message;
            }

            return message;
        }
        static string ParseToSQLQuery(SqlQuery sqlQuery, string query)
        {
            if (query.ToLower().Contains("@contexttotext"))
            {
                Boolean isFound = true;
                int position = 0;

                // This function can occur more than once in the query, process them all
                do
                {
                    if (query.ToLower().IndexOf("@contexttotext", position) > 0)
                    {
                        CustomFunction customFunction = new CustomFunction(Enums.CustomFunctions.ContextToText);

                        position = query.ToLower().IndexOf("@contexttotext", position);
                        customFunction.StartPositionFunction = position;

                        // Retrieve the fieldname from which later the context will be retrieved
                        customFunction.MessageField = Utils.RetrieveField(customFunction, query, "@ContextToText").MessageField;

                        // Retrieve eventually requested properties, which have to be returned
                        customFunction.ContextProperties = Utils.RetrieveCtxProperties(customFunction, query, "@ContextToText");

                        // Add alias
                        query = Utils.AddAlias(customFunction, query);

                        // Remove the @ContextToText function from the query
                        query = Utils.RemoveFunction(customFunction, query, "@ContextToText");

                        // Retrieve eventually requested properties, which have to be returned
                        //customFunction.ContextProperties = Utils.RetrieveCtxProperties(query, "@ContextToText");

                        sqlQuery.CustomFunctions.Add(customFunction.Key, customFunction);
                    }
                    else
                    {
                        isFound = false;
                    }
                }
                while (isFound);

            }
            if (query.ToLower().Contains("@messagetotext"))
            {
                Boolean isFound = true;
                int position = 0;

                // This custom function can occur more than once in the query, process them all
                do
                {
                    if (query.ToLower().IndexOf("@messagetotext", position) > 0)
                    {

                        // Create a new custom function
                        CustomFunction customFunction = new CustomFunction(Enums.CustomFunctions.MessageToText);

                        position = query.ToLower().IndexOf("@messagetotext", position);
                        customFunction.StartPositionFunction = position;

                        // Retrieve the fieldname from which later the message will be retrieved
                        customFunction.MessageField = Utils.RetrieveField(customFunction, query, "@MessageToText").MessageField;

                        // Retrieve the eventually entered ebXmlOption
                        customFunction.EbXmlOption = Utils.RetrieveEbXMLOption(customFunction, query);

                        // Retrieve eventually entered XPath query
                        customFunction.Query = Utils.RetrieveXPathQuery(customFunction, query, "@MessageToText");

                        // Add alias
                        query = Utils.AddAlias(customFunction, query);

                        // Remove the @MessageToText function from the query
                        query = Utils.RemoveFunction(customFunction, query, "@MessageToText");

                        //sqlQuery.CustomFunctions.Add(customFunction.MessageField, customFunction);
                        sqlQuery.CustomFunctions.Add(customFunction.Key, customFunction);
                    }
                    else
                    {
                        isFound = false;
                    }
                }
                while (isFound);
            }
            if (query.ToLower().Contains("@bytearray"))
            {
                Boolean isFound = true;
                int position = 0;

                // This custom function can occur more than once in the query, process them all
                do
                {
                    if (query.ToLower().IndexOf("@bytearray", position) > 0)
                    {
                        CustomFunction customFunction = new CustomFunction(Enums.CustomFunctions.ByteArray);

                        position = query.ToLower().IndexOf("@bytearray", position);
                        customFunction.StartPositionFunction = position;

                        // Retrieve the fieldname from which later the message will be retrieved
                        customFunction.MessageField = Utils.RetrieveField(customFunction, query, "@ByteArray").MessageField;

                        // Retrieve the eventually entered ebXmlOption
                        customFunction.EbXmlOption = Utils.RetrieveEbXMLOption(customFunction, query);

                        // Retrieve eventually entered XPath query
                        customFunction.Query = Utils.RetrieveXPathQuery(customFunction, query, "@ByteArray");

                        // Add alias
                        query = Utils.AddAlias(customFunction, query);

                        // Remove the @MessageToText function from the query
                        query = Utils.RemoveFunction(customFunction, query, "@ByteArray");

                        sqlQuery.CustomFunctions.Add(customFunction.Key, customFunction);
                    }
                    else
                    {
                        isFound = false;
                    }
                }
                while (isFound);
            }
            return query;
        }
        static bool ParseQuery(string str)
        {
            if (str.ToLower().Contains("@contexttotext"))
            {
                return true;
            }
            if (str.ToLower().Contains("@messagetotext"))
            {
                return true;
            }
            if (str.ToLower().Contains("@bytearray"))
            {
                return true;
            }
            return false;
        }
        static string ParseXML(SqlQuery sqlQuery, string XML, string xpath, bool hideOuterXMLTag)
        {
            string res = null;

            try
            {
                if (xpath != null)
                {
                    XmlDocument o = new XmlDocument();
                    o.LoadXml(XML);
                    XmlNamespaceManager manager = new XmlNamespaceManager(o.NameTable);

                    if (sqlQuery.Namespaces.Count > 0)
                    {
                        foreach (DictionaryEntry entry in sqlQuery.Namespaces)
                        {
                            manager.AddNamespace(entry.Key.ToString(), entry.Value.ToString());
                        }
                    }

                    XmlNode node = null;
                    if (sqlQuery.Namespaces.Count > 0)
                    {
                        node = o.SelectSingleNode(xpath, manager);
                    }
                    else
                    {
                        node = o.SelectSingleNode(xpath);
                    }

                    if (node == null)
                    {
                        res = Constants.NULL_VALUE;
                    }
                    else
                    {
                        res = hideOuterXMLTag == true ? node.InnerXml : node.OuterXml;
                    }
                }
                else
                {
                    return XML;
                }
            }
            catch (Exception ex)
            {
                res = ex.Message;
            }
            return res;

        }
        static public string PreProcessQuery(string sql, int nrOfRecords)
        {
            if (sql.StartsWith("select top") == false)
            {
                if (sql.StartsWith("select ") == true)
                {
                    sql = "select top " + nrOfRecords.ToString() + sql.Substring(6);
                }
            }
            else
            {
                int pos = sql.IndexOf(' ', 11);
                sql = "select top " + nrOfRecords.ToString() + sql.Substring(pos);
            }
            return sql;

        }
        static string RemoveFunction(CustomFunction customFunction, string str, string function)
        {

            int posStartRemove = 1;

            // First remove the function and the opening bracket
            //int pos = str.IndexOf(function, 0);
            int pos = str.IndexOf(function, customFunction.StartPositionFunction);
            str = str.Remove(pos, function.Length + 1);

            // Search for semicolon, if found remove from that position
            int posSemicolon = str.IndexOf(";", pos);

            int posClosingBracket = str.IndexOf(")", pos);

            if (posSemicolon > 0 & posSemicolon < posClosingBracket)
            {

                //int posClosingBracket = str.IndexOf(")", pos);

                posStartRemove = posSemicolon;
                str = str.Remove(posStartRemove, posClosingBracket - posSemicolon + 1);
            }
            else
            {
                // Search for the closing bracket and remove it
                posStartRemove = str.IndexOf(")", pos);
                str = str.Remove(posStartRemove, 1);
            }
            return str;
        }
        static string RetrieveCtxProperties(CustomFunction customFunction, string str, string function)
        {

            string context = null;

            // Determine position of function
            //int posStartFunction = str.IndexOf(function, 0);
            int posStartFunction = str.IndexOf(function, customFunction.StartPositionFunction);
            
            // Increase with length of function name + 1 (for the opening bracket)
            posStartFunction += function.Length + 1;

            // Search for position from closing bracket
            int posClosingBracket = str.IndexOf(")", posStartFunction);

            // Search for position from semicolon
            int posSemicolon = str.IndexOf(";", posStartFunction);

            if (posSemicolon > 0 && posSemicolon < posClosingBracket)
            {

                // retrieve the context properties
                context = str.Substring(posSemicolon + 1, posClosingBracket - posSemicolon - 1);
            }
            return context;
        }
        static string RetrieveData(string xml, string xPathQuery, System.Collections.Hashtable namespaces, Boolean hideOuterXML)
        {

            XmlDocument xmlDocument = new System.Xml.XmlDocument();
            xmlDocument.LoadXml(xml);

            XmlNamespaceManager manager = new XmlNamespaceManager(xmlDocument.NameTable);
            foreach (DictionaryEntry entry in namespaces)
            {
                manager.AddNamespace(entry.Key.ToString(), entry.Value.ToString());
            }

            XmlNode node = xmlDocument.SelectSingleNode(xPathQuery, manager);

            if (node != null)
            {
                return hideOuterXML == true ? node.InnerXml : node.OuterXml;
            }
            else
            {
                return Constants.NULL_VALUE;
            }
        }
        static string RetrieveEbXMLOption(CustomFunction customFunction, string str)
        {
            string function = "@MessageToText";
            //int posStartFunction = str.IndexOf(function, 0);
            int posStartFunction = str.IndexOf(function, customFunction.StartPositionFunction);

            // Increase with length of function name + 1 (for the opening bracket)
            posStartFunction += function.Length + 1;

            // Retrieve first semicolon (seperator between Database and ebXMLOption or XPath Query)
            int posSemicolon = str.IndexOf(";", posStartFunction);
            int posStartXPathQuery = -1;

            if (posSemicolon != -1)
            {
                // Retrieve start position of XPath Query
                posStartXPathQuery = str.IndexOf(Convert.ToChar(34), posSemicolon);
            }

            // Check if XPath Query exists
            if (posStartXPathQuery == -1)
            {
                // No XPath Query, so no useable ebXML option
                return "";
            }

            // Determine ebXML option
            string sub = str.Substring(posSemicolon, posStartXPathQuery - posSemicolon).ToLower();
            if (sub.IndexOf("ebenvelope") != -1)
            {
                return "ebenvelope";
            }

            if (sub.IndexOf("ebpayload") != -1)
            {
                return "ebpayload";
            }
            return "";
        }
        static CustomFunction RetrieveField(CustomFunction customFunction, string str, string function)
        {
            // Determine position of function
            //int posStartFunction = str.IndexOf(function, 0);
            int posStartFunction = str.IndexOf(function, customFunction.StartPositionFunction);

            // Increase with length of function name + 1 (for the opening bracket)
            posStartFunction += function.Length + 1;

           int posSemicolon = str.IndexOf(";", posStartFunction);

            // Search for position from closing bracket
            int posClosingBracket = str.IndexOf(")", posStartFunction);

            if (posSemicolon > 0 && posSemicolon < posClosingBracket)
            {
                int len = posSemicolon - posStartFunction;
                str = str.Substring(posStartFunction, len);

                // Check if the field contains a column alias
                if(str.Contains(" "))
                {
                    str = str.Substring(str.IndexOf(" ") + 1);
                    customFunction.ColumnAlias = str;
                }
            }
            else
            {

                // Search for position from closing bracket
                //int posClosingBracket = str.IndexOf(")", posStartFunction);

                int len = posClosingBracket - posStartFunction;

                str = str.Substring(posStartFunction, len);

                // Check if the field contains a column alias
                if (str.Contains(" "))
                {
                    str = str.Substring(str.IndexOf(" ") + 1);
                    customFunction.ColumnAlias = str;
                }
            }

            // Remove table aliases
            if (str.IndexOf(".", 0) > 0)
            {
                string alias = str.Substring(0, str.IndexOf(".", 0) + 1);
                str = str.Remove(0, str.IndexOf(".", 0) + 1);
                customFunction.TableAlias = alias;
            }

            customFunction.MessageField = str;
            customFunction.StartPosition = posStartFunction;
            return customFunction;
        }
        static string RetrieveXPathQuery(CustomFunction customFunction, string str, string function)
        {
            string query = null;

            // Determine position of function
            //int posStartFunction = str.IndexOf(function, 0);
            int posStartFunction = str.IndexOf(function, customFunction.StartPositionFunction);

            // Increase with length of function name + 1 (for the opening bracket)
            posStartFunction += function.Length + 1;

            if (customFunction.EbXmlOption != null)
            {
                posStartFunction += customFunction.EbXmlOption.Length + 1;
            }

            // Search for position from semicolon
            int posSemicolon = str.IndexOf(";", posStartFunction);

            if (posSemicolon > 0)
            {
                // Search for position from closing bracket
                string tmp = str.Substring(posStartFunction);
                int i1 = tmp.IndexOf(Convert.ToChar(34));
                int i2 = tmp.IndexOf(Convert.ToChar(34), 1);
                //int posClosingBracket = str.IndexOf("]", posStartFunction);
                int posClosingBracket = str.IndexOf("\"", posStartFunction);
                posClosingBracket = str.IndexOf("\"", posClosingBracket + 1);

                // retrieve the XPath query
                query = str.Substring(posSemicolon + 2, posClosingBracket - posSemicolon - 2);
            }
            return query;

        }
        #endregion
    }
}
